package com.shengruan.admin.jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.shengruan.admin.vo.Question;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.shengruan.framework.jdbc.BaseJdbc;
import com.shengruan.framework.jdbc.BeanHandler;
import com.shengruan.framework.jdbc.BeanListHandler;

public class QuestionJdbc  extends BaseJdbc{

    Logger logger = LoggerFactory.getLogger(getClass());

    public static final String QUESTION_INSERT = "insert into question (name, content, createTime)values(?, ?, ?)";


    public List<Question> loadAll() {
        List<Question> questionList = new ArrayList<Question>();
        Connection conn = this.getConnection();
        StringBuilder sql = new StringBuilder("select * from question order by id ASC");
        try
		{
			questionList = this.query(conn, sql.toString(), new BeanListHandler<Question>(Question.class));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return questionList;
    }

    public List<Question> loadAll(int userId) {
        List<Question> questionList = new ArrayList<Question>();
        Connection conn = this.getConnection();
        StringBuilder sql = new StringBuilder("SELECT * FROM question q WHERE q.userId IS NULL OR q.userId = ? ORDER BY q.id ASC");
        Object[] pram = {userId};
        try
		{
			questionList = this.query(conn, sql.toString(), new BeanListHandler<Question>(Question.class), pram);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return questionList;
    }

    

    public void save(Question question) {
        Connection conn = this.getConnection();
        Object[] pram = {question.getName(), question.getContent(), question.getCreateTime()};
        try {
            this.execute(conn, QUESTION_INSERT, pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }


    public void update(Question question){
		Connection conn = this.getConnection();
		String sql = "update question set name=?, content=?, createTime=? where id=?";
		try {
			this.execute(conn, sql, question.getName(),question.getContent(), question.getCreateTime(), question.getId());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
	}

    public int getMAXRows(int userId) {
        Connection conn = this.getConnection();
        String sql = "SELECT MAX(ID) FROM question q WHERE q.userId = ?";
        Object[] pram = {userId};
        int count = 0;
        try {
            count = this.getRows(conn, sql, pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return count;
    }


    public Question getById(int id){
		Connection conn = this.getConnection();
		Question question =null;
		String sql = "select * from question where id = ?";
		try {
			question = this.query(conn, sql,new BeanHandler<Question>(Question.class), id);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return question;
	}


    public void deleteById(int id)
	{
		Connection conn = this.getConnection();
		String sql = "delete from question where id = ? ";
		try {
			this.execute(conn, sql, id);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
	}
}
